package com.feng.service;

import com.feng.dao.BillDao;
import com.feng.dao.WrongBillDao;
import com.feng.model.Bill;
import com.feng.model.StevenBill;
import com.feng.util.CellStruct;
import com.feng.util.CompareRet;
import com.feng.util.ExcelReader;
import com.feng.util.PatternUtil;
import org.apache.log4j.LogManager;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import java.util.Collection;
import java.util.Iterator;
import java.util.List;

/**
 * 代理商Service
 */
@Service
public class StevenService extends BaseService<Bill>{

	protected static Logger log = LogManager.getLogger(StevenService.class);


	@Autowired
	protected BillDao billDao;

	@Autowired
	protected WrongBillDao wrongBillDao;

	public static CellStyle cellStyleRed ;
	public static CellStyle cellStyleGreen ;
	public void initStyle(Workbook wb){

		cellStyleRed = wb.createCellStyle();
		Font fontRed = wb.createFont();
		fontRed.setColor(Font.COLOR_RED);
		cellStyleRed.setFont(fontRed);

		cellStyleGreen = wb.createCellStyle();
		Font fontGreen = wb.createFont();
		fontGreen.setColor(HSSFColor.GREEN.index);
		cellStyleGreen.setFont(fontGreen);
	}

	private StevenBill getStevenBill(Collection<StevenBill> list,String bussinessNo,String compulsoryNo,String plateNo){
		if(bussinessNo!=null){
			for(StevenBill bill:list){
				if(bussinessNo.equalsIgnoreCase(bill.bussinessNo)){
					return bill;
				}
			}
		}else if(compulsoryNo!=null){
			for(StevenBill bill:list){
				if(compulsoryNo.equalsIgnoreCase(bill.compulsoryNo)){
					return bill;
				}
			}
		}else if(plateNo!=null){
			for(StevenBill bill:list){
				if(plateNo.equalsIgnoreCase(bill.plateNo)){
					return bill;
				}
			}
		}

		return null;
	}

	private CompareRet compareStevenBill(Collection<StevenBill> list,StevenBill bill ){
		CompareRet compareRet = new CompareRet();
		if(!StringUtils.isEmpty(bill.bussinessNo)){
			StevenBill item = getStevenBill(list,bill.bussinessNo,null,null);
			//未找到对应的商业保单号
			if(item == null){
				compareRet.bussinessNoCell = new CellStruct(false,"不存在",cellStyleRed);
			}else {
				//商业保单号存在的情况下比较金额
				compareRet.bussinessNoCell = new CellStruct(true,"存在",cellStyleGreen);

				if(item.bussinessAmount == null && bill.bussinessAmount == null){

				}else if(ExcelReader.isEqual(item.bussinessAmount,bill.bussinessAmount)){
					compareRet.bussinessAmountCell = new CellStruct(true,"一致",cellStyleGreen);
				}else{
					compareRet.bussinessAmountCell = new CellStruct(false,"不一致:"+item.bussinessAmount,cellStyleRed);
				}

				if(StringUtils.isEmpty(item.plateNo) && StringUtils.isEmpty(bill.plateNo) ){
					compareRet.plateNoCell = new CellStruct(false,"无车牌号",cellStyleRed);
				}else if(ExcelReader.isEqual(item.plateNo,bill.plateNo)){
					compareRet.plateNoCell = new CellStruct(true,"车牌号一致",cellStyleGreen);
				}else{
					compareRet.plateNoCell = new CellStruct(false,"车牌号不一致",cellStyleRed);
				}
			}
		}
		if(!StringUtils.isEmpty(bill.compulsoryNo) ){
			StevenBill item = getStevenBill(list,null,bill.compulsoryNo,null);
			//未找到对应的商业保单号
			if(item == null){
				compareRet.compulsoryNoCell = new CellStruct(false,"不存在",cellStyleRed);
			}else {
				compareRet.compulsoryNoCell = new CellStruct(true,"存在",cellStyleGreen);

				if(item.compulsoryAmount == null && bill.compulsoryAmount == null){

				}else if(ExcelReader.isEqual(item.compulsoryAmount,bill.compulsoryAmount)){
					compareRet.compulsoryAmountCell = new CellStruct(true,"一致",cellStyleGreen);
				}else{
					compareRet.compulsoryAmountCell = new CellStruct(false,"不一致:"+item.compulsoryAmount,cellStyleRed);
				}

				if(StringUtils.isEmpty(item.plateNo) && StringUtils.isEmpty(bill.plateNo) ){
					compareRet.plateNoCell = new CellStruct(false,"无车牌号",cellStyleRed);
				}else if(ExcelReader.isEqual(item.plateNo,bill.plateNo)){
					compareRet.plateNoCell = new CellStruct(true,"车牌号一致",cellStyleGreen);
				}else{
					compareRet.plateNoCell = new CellStruct(false,"车牌号不一致",cellStyleRed);
				}
			}
		}
		if(!StringUtils.isEmpty(bill.plateNo) && (compareRet.compulsoryNoCell==null ||!compareRet.compulsoryNoCell.exists) && (compareRet.bussinessNoCell==null ||!compareRet.bussinessNoCell.exists) ){
			StevenBill item = getStevenBill(list,null,null,bill.plateNo);
			//未找到对应的商业保单号
			if(item == null){
				compareRet.plateNoCell = new CellStruct(false,"不存在",cellStyleRed);
			}else {
				compareRet.plateNoCell = new CellStruct(true,"存在",cellStyleGreen);

				if(item.bussinessAmount == null && bill.bussinessAmount == null){

				}else if(ExcelReader.isEqual(item.bussinessAmount,bill.bussinessAmount)){
					compareRet.bussinessAmountCell = new CellStruct(true,"一致",cellStyleGreen);
				}else{
					compareRet.bussinessAmountCell = new CellStruct(false,"不一致:"+item.bussinessAmount,cellStyleRed);
				}
				if(item.compulsoryAmount == null && bill.compulsoryAmount == null){

				}else if(ExcelReader.isEqual(item.compulsoryAmount,bill.compulsoryAmount)){
					compareRet.compulsoryAmountCell = new CellStruct(true,"一致",cellStyleGreen);
				}else{
					compareRet.compulsoryAmountCell = new CellStruct(false,"不一致:"+item.compulsoryAmount,cellStyleRed);
				}
			}
		}
		return compareRet;
	}


	private void compareVal(Cell cell,Double M,Double O){
		if(M == null && O == null){

		}else if(ExcelReader.isEqual(M,O)){
			cell.setCellStyle(cellStyleGreen);
			cell.setCellValue("一致");
		}else{
			cell.setCellStyle(cellStyleRed);
			cell.setCellValue("不一致:"+M);
		}
	}

	//台帐账单,交强与商业在一行
	public void parseBillTotal(Sheet sheet,List<StevenBill> bills) throws Exception{
		Integer i_bussi_no = null,i_compu_no = null,i_bussi_amount = null,i_compu_amount = null,i_bussiDate = null,i_compuDate = null,i_license_plate = null;
		Row firstRow = sheet.getRow(0);
		Iterator<Cell> iterator = firstRow.cellIterator();
		while(iterator.hasNext()){
			Cell cell = iterator.next();
			String title = cell.getStringCellValue();
			if(PatternUtil.pass(title,PatternUtil.plateNo)){
				i_license_plate = cell.getColumnIndex();
			}else if(PatternUtil.pass(title,PatternUtil.bussiNo)){
				i_bussi_no = cell.getColumnIndex();
			}else if(PatternUtil.pass(title,PatternUtil.bussiAmount)){
				i_bussi_amount = cell.getColumnIndex();
			}else if(PatternUtil.pass(title,PatternUtil.bussiDate)){
				i_bussiDate = cell.getColumnIndex();
			}else if(PatternUtil.pass(title,PatternUtil.compulsoryNo)){
				i_compu_no = cell.getColumnIndex();
			}else if(PatternUtil.pass(title,PatternUtil.compulsoryAmount)){
				i_compu_amount = cell.getColumnIndex();
			}else if(PatternUtil.pass(title,PatternUtil.compulsoryDate)){
				i_compuDate = cell.getColumnIndex();
			}
		}
		if(i_bussi_no == null||i_compu_no==null||i_bussi_amount==null||i_compu_amount==null||i_bussiDate==null||i_compuDate==null||i_license_plate==null){
			throw new Exception("台帐账单格式错误,请确认!") ;
		}
		for(int rowIndex =1,totalRow = sheet.getLastRowNum();rowIndex<=totalRow;rowIndex++) {
			Row row = sheet.getRow(rowIndex);
			if(row==null) continue;
			String license_plate = ExcelReader.getString(row.getCell(i_license_plate));

			Double bussi_amount = ExcelReader.getDouble(row.getCell(i_bussi_amount));
			Double compu_amount =ExcelReader.getDouble(row.getCell(i_compu_amount));
			System.out.println("curRow:"+license_plate);
			if(bussi_amount == null&&compu_amount == null){
				System.out.println("curRow:"+license_plate+":passed");
				continue;
			}

			String bussi_no =  ExcelReader.getString(row.getCell(i_bussi_no));
			String compu_no =  ExcelReader.getString(row.getCell(i_compu_no));

			String bussiDate = ExcelReader.getString(row.getCell(i_bussiDate));
			String compuDate = ExcelReader.getString(row.getCell(i_compuDate));

			StevenBill bill = new StevenBill();
			bill.plateNo = license_plate;
			if(bussi_amount != null){
				if(bussi_no.indexOf("'")>-1){
					System.err.println(bussi_no);
				}
				bussi_no = bussi_no.replaceAll("\\W","");
				bill.bussinessNo = bussi_no;
				bill.bussinessAmount = bussi_amount;
				bill.bussinessDate = bussiDate;
			}
			if(compu_amount != null){
				if(compu_no.indexOf("'")>-1){
					System.err.println(compu_no);
				}
				compu_no = compu_no.replaceAll("\\W","");
				bill.compulsoryNo = compu_no;
				bill.compulsoryAmount = compu_amount;
				bill.compulsoryDate = compuDate;
			}
			bills.add(bill);
		}
	}

	//保司账单,交强与商业在两行
	public void parseBillBaosi(Sheet sheet,List<StevenBill> bills) throws Exception{
		Integer i_amount = null,i_no = null,i_date = null,i_license_plate = null;
		Row firstRow = sheet.getRow(0);
		Iterator<Cell> iterator = firstRow.cellIterator();
		while(iterator.hasNext()){
			Cell cell = iterator.next();
			String title = cell.getStringCellValue();
			if(title.indexOf("车牌")>-1){
				i_license_plate = cell.getColumnIndex();
			}else if(title.indexOf("保单号")>-1){
				i_no = cell.getColumnIndex();
			}else if(title.indexOf("时间")>-1){
				i_date = cell.getColumnIndex();
			}else if(title.indexOf("保费")>-1){
				i_amount = cell.getColumnIndex();
			}
		}
		if(i_amount == null||i_no==null||i_license_plate==null){
			throw new Exception("保司账单格式错误,请确认!") ;
		}
		for(int rowIndex =1,totalRow = sheet.getLastRowNum();rowIndex<=totalRow;rowIndex++) {
			Row row = sheet.getRow(rowIndex);
			if(row==null) continue;
			String license_plate = ExcelReader.getString(row.getCell(i_license_plate));
			String no =  ExcelReader.getString(row.getCell(i_no));

			System.out.println("curRow:"+license_plate);

			if(StringUtils.isEmpty(no)){
				continue;
			}
			String date = ExcelReader.getString(row.getCell(i_date));
			Double amount = ExcelReader.getDouble(row.getCell(i_amount));
			StevenBill bill = new StevenBill();
			bill.plateNo = license_plate;
			if(no.indexOf("DZA")>-1){
				bill.compulsoryNo = no;
				bill.compulsoryAmount = amount;
				bill.compulsoryDate = date;
			}else{
				bill.bussinessNo = no;
				bill.bussinessAmount = amount;
				bill.bussinessDate = date;
			}
			bills.add(bill);
		}
	}

	//台帐账单,交强与商业在一行 ,比较
	public void compareBillTotal(Sheet sheet,List<StevenBill> list) throws Exception{
		Integer i_bussi_no = null,i_compu_no = null,i_bussi_amount = null,i_compu_amount = null,i_bussiDate = null,i_compuDate = null,i_license_plate = null;
		Row firstRow = sheet.getRow(0);
		Iterator<Cell> iterator = firstRow.cellIterator();
		while(iterator.hasNext()){
			Cell cell = iterator.next();
			String title = cell.getStringCellValue();
			if(PatternUtil.pass(title,PatternUtil.plateNo)){
				i_license_plate = cell.getColumnIndex();
			}else if(PatternUtil.pass(title,PatternUtil.bussiNo)){
				i_bussi_no = cell.getColumnIndex();
			}else if(PatternUtil.pass(title,PatternUtil.bussiAmount)){
				i_bussi_amount = cell.getColumnIndex();
			}else if(PatternUtil.pass(title,PatternUtil.bussiDate)){
				i_bussiDate = cell.getColumnIndex();
			}else if(PatternUtil.pass(title,PatternUtil.compulsoryNo)){
				i_compu_no = cell.getColumnIndex();
			}else if(PatternUtil.pass(title,PatternUtil.compulsoryAmount)){
				i_compu_amount = cell.getColumnIndex();
			}else if(PatternUtil.pass(title,PatternUtil.compulsoryDate)){
				i_compuDate = cell.getColumnIndex();
			}
		}
		if(i_bussi_no == null||i_compu_no==null||i_bussi_amount==null||i_compu_amount==null||i_bussiDate==null||i_compuDate==null||i_license_plate==null){
			throw new Exception("台帐账单格式错误,请确认!") ;
		}
		int totalCellNum = firstRow.getLastCellNum();
		Cell cell_plate =  firstRow.createCell(totalCellNum + 1);
		Cell cell_bussiness =  firstRow.createCell(totalCellNum+2);
		Cell cell_bussiness_amount =  firstRow.createCell(totalCellNum+3);
		Cell cell_compulsory =  firstRow.createCell(totalCellNum+4);
		Cell cell_compulsory_amount =  firstRow.createCell(totalCellNum+5);

		Cell cell_no = firstRow.getCell(0);
		CellStyle style = cell_no.getCellStyle();

		cell_plate.setCellValue("车牌号是否存在");
		cell_bussiness.setCellValue("商业保单号");
		cell_bussiness_amount.setCellValue("商业险金额");
		cell_compulsory.setCellValue("交强保单号");
		cell_compulsory_amount.setCellValue("交强险金额");

		cell_plate.setCellStyle(style);
		cell_compulsory.setCellStyle(style);
		cell_compulsory_amount.setCellStyle(style);
		cell_bussiness.setCellStyle(style);
		cell_bussiness_amount.setCellStyle(style);

		for(int rowIndex =1,totalRow = sheet.getLastRowNum();rowIndex<=totalRow;rowIndex++) {
			Row row = sheet.getRow(rowIndex);
			if(row==null) continue;
			String license_plate = ExcelReader.getString(row.getCell(i_license_plate));
			String bussi_no =  ExcelReader.getString(row.getCell(i_bussi_no));
			String compu_no =  ExcelReader.getString(row.getCell(i_compu_no));

			Double bussi_amount = ExcelReader.getDouble(row.getCell(i_bussi_amount));
			Double compu_amount =ExcelReader.getDouble(row.getCell(i_compu_amount));

			System.out.println("curRow:"+license_plate);
			if(StringUtils.isEmpty(bussi_no) && StringUtils.isEmpty(compu_no) && StringUtils.isEmpty(license_plate)){
				continue;
			}

			StevenBill bill = new StevenBill();
			bill.plateNo = license_plate;
			if(bussi_amount != null){
				if(bussi_no.indexOf("'")>-1){
					System.err.println(bussi_no);
				}
				bussi_no = bussi_no.replaceAll("\\W","");
				bill.bussinessNo = bussi_no;
				bill.bussinessAmount = bussi_amount;
			}
			if(compu_amount != null){
				if(compu_no.indexOf("'")>-1){
					System.err.println(compu_no);
				}
				compu_no = compu_no.replaceAll("\\W","");
				bill.compulsoryNo = compu_no;
				bill.compulsoryAmount = compu_amount;
			}

			// 获取到Excel文件中的所有行数
			CompareRet compareRet = compareStevenBill(list,bill);

			compareRet.fillCell(row.createCell(totalCellNum + 1),compareRet.plateNoCell);
			compareRet.fillCell(row.createCell(totalCellNum + 2),compareRet.bussinessNoCell);
			compareRet.fillCell(row.createCell(totalCellNum + 3),compareRet.bussinessAmountCell);
			compareRet.fillCell(row.createCell(totalCellNum + 4),compareRet.compulsoryNoCell);
			compareRet.fillCell(row.createCell(totalCellNum + 5),compareRet.compulsoryAmountCell);

		}
	}

	//台帐账单,交强与商业在两行 ,比较
	public void compareBillBaosi(Sheet sheet,List<StevenBill> list) throws Exception{
		Integer i_no = null,i_amount = null,i_plate = null;
		Row firstRow = sheet.getRow(0);
		Iterator<Cell> iterator = firstRow.cellIterator();
		while(iterator.hasNext()){
			Cell cell = iterator.next();
			String title = cell.getStringCellValue();
			if(title.indexOf("车牌")>-1){
				i_plate = cell.getColumnIndex();
			}else if(title.indexOf("保单号")>-1){
				i_no = cell.getColumnIndex();
			}else if(title.indexOf("保费")>-1){
				i_amount = cell.getColumnIndex();
			}
		}
		if(i_plate == null||i_no==null||i_amount==null){
			throw new Exception("保司账单格式错误,请确认!") ;
		}
		int  totalCellNum = firstRow.getLastCellNum();
		Cell cell_plate =  firstRow.createCell(totalCellNum + 1);
		Cell cell_no =  firstRow.createCell(totalCellNum+2);
		Cell cell_amount =  firstRow.createCell(totalCellNum+3);

		Cell cell_0 = firstRow.getCell(0);
		CellStyle style = cell_0.getCellStyle();

		cell_plate.setCellValue("车牌号是否存在");
		cell_no.setCellValue("保单号");
		cell_amount.setCellValue("金额");

		cell_plate.setCellStyle(style);
		cell_no.setCellStyle(style);
		cell_amount.setCellStyle(style);

		for(int rowIndex =1,totalRow = sheet.getLastRowNum();rowIndex<=totalRow;rowIndex++) {
			Row row = sheet.getRow(rowIndex);
			if(row==null) continue;
			String plateNo = ExcelReader.getString(row.getCell(i_plate));
			String no =  ExcelReader.getString(row.getCell(i_no));
			System.out.println("curRow:"+plateNo);
			if(StringUtils.isEmpty(no)){
				continue;
			}
			Double amount = ExcelReader.getDouble(row.getCell(i_amount));
			StevenBill bill = new StevenBill();
			bill.plateNo = plateNo;
			if(no.indexOf("'")>-1){
				System.err.println(no);
			}
			no = no.replaceAll("\\W","");

			if(no.indexOf("DZA")>-1) {
				bill.compulsoryNo = no;
				bill.compulsoryAmount = amount;
			}else{
				bill.bussinessNo = no;
				bill.bussinessAmount = amount;
			}
			CompareRet compareRet = compareStevenBill(list,bill);

			// 获取到Excel文件中的所有行数
			// 遍历行 从第二行开始遍历
			compareRet.fillCell(row.createCell(totalCellNum + 1),compareRet.plateNoCell);
			if(no.indexOf("DZA")>-1) {
				compareRet.fillCell(row.createCell(totalCellNum + 2),compareRet.compulsoryNoCell);
				compareRet.fillCell(row.createCell(totalCellNum + 3),compareRet.compulsoryAmountCell);
			}else{
				compareRet.fillCell(row.createCell(totalCellNum + 2),compareRet.bussinessNoCell);
				compareRet.fillCell(row.createCell(totalCellNum + 3),compareRet.bussinessAmountCell);
			}
		}
	}
}
